using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Reflection;
using System.Xml;

using NBear.Common;

namespace NBear.Tools.Configurator
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            entityTree.TreeViewNodeSorter = new NodeComparer();
        }

        private Dictionary<string, EntityConfiguration> entityDic = new Dictionary<string, EntityConfiguration>();
        private Dictionary<string, Dictionary<string, ColumnConfiguration>> columnDic = new Dictionary<string, Dictionary<string, ColumnConfiguration>>();
        private Dictionary<string, List<string>> parentEntityDic = new Dictionary<string, List<string>>();

        private void loadAssemblyToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (openAssemblyFileDialog.ShowDialog() == DialogResult.OK)
            {
                DoLoadAssembly(openAssemblyFileDialog.FileName);
            }
        }

        private void DoLoadAssembly(string fileName)
        {
            try
            {
                Assembly ass = Assembly.LoadFile(fileName);

                if (!entityTree.Nodes.ContainsKey(ass.FullName))
                {
                    entityTree.Nodes.Add(ass.FullName, fileName);
                }
                else
                {
                    entityTree.Nodes.Find(ass.FullName, false)[0].Text = fileName;
                    entityTree.Nodes.Find(ass.FullName, false)[0].Nodes.Clear();
                }

                TreeNode assNode = entityTree.Nodes.Find(ass.FullName, false)[0];
                foreach (Type type in ass.GetTypes())
                {
                    if (type.IsInterface && typeof(IEntity).IsAssignableFrom(type) && typeof(IEntity) != type)
                    {
                        if (!assNode.Nodes.ContainsKey(type.FullName))
                        {
                            assNode.Nodes.Add(type.FullName, type.Name);
                        }
                        else
                        {
                            assNode.Nodes.Find(type.FullName, false)[0].Nodes.Clear();
                        }

                        ParseParentInterfaces(type);

                        if (!entityDic.ContainsKey(type.FullName))
                        {
                            EntityConfiguration ec = new EntityConfiguration(type.FullName);

                            //get table attribute
                            object[] attrs = type.GetCustomAttributes(typeof(TableAttribute), false);
                            if (attrs != null && attrs.Length > 0)
                            {
                                TableAttribute ta = (TableAttribute)attrs[0];
                                ec.AdditionalInsert = ta.AdditionalInsert;
                                ec.AdditionalWhere = ta.AdditionalWhere;
                                ec.IsContract = ta.IsContract;
                                ec.IsView = ta.IsView;
                                if (ta.Name != null)
                                {
                                    ec.TableName = ta.Name.Replace("#_Begin# (", "").Replace(") __AliasName #_End#", "");
                                }
                                ec.IsCascadeUpdate = ta.IsCascadeUpdate;
                                ec.UpdateTableName = ta.UpdateTableName;
                            }

                            //get stored procedure attribute
                            attrs = type.GetCustomAttributes(typeof(StoreProcedureAttribute), false);
                            if (attrs != null && attrs.Length > 0)
                            {
                                StoreProcedureAttribute spa = (StoreProcedureAttribute)attrs[0];
                                ec.StoredProcedure = spa.Name ?? type.Name;
                                ec.StoredProcedureFillByIndex = spa.FillByIndex;
                            }

                            entityDic.Add(type.FullName, ec);
                        }

                        if (!columnDic.ContainsKey(type.FullName))
                        {
                            columnDic.Add(type.FullName, new Dictionary<string, ColumnConfiguration>());
                        }

                        TreeNode entityNode = assNode.Nodes.Find(type.FullName, false)[0];

                        foreach (PropertyInfo pi in type.GetProperties())
                        {
                            entityNode.Nodes.Add(pi.Name, pi.Name);

                            if (!columnDic[type.FullName].ContainsKey(pi.Name))
                            {
                                ColumnConfiguration cc = new ColumnConfiguration(pi.Name);

                                object[] attrs = pi.GetCustomAttributes(typeof(ColumnNameAttribute), false);
                                if (attrs != null && attrs.Length > 0)
                                {
                                    ColumnNameAttribute ca = (ColumnNameAttribute)attrs[0];
                                    cc.ColumnName = ca.Name;
                                }

                                attrs = pi.GetCustomAttributes(typeof(PrimaryKeyAttribute), false);
                                if (attrs != null && attrs.Length > 0)
                                {
                                    cc.IsPrimaryKey = true;
                                }

                                attrs = pi.GetCustomAttributes(typeof(CustomPropertyAttribute), false);
                                if (attrs != null && attrs.Length > 0)
                                {
                                    CustomPropertyAttribute cpa = (CustomPropertyAttribute)attrs[0];
                                    cc.IsCustomProperty = true;
                                    cc.Parameters = cpa.Parameters;
                                }

                                columnDic[type.FullName].Add(pi.Name, cc);
                            }
                        }
                    }
                }

                entityTree.Sort();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Assembly to be loaded must be signed with strong name.\r\n\r\n" + ex.ToString());
            }
        }

        private void ParseParentInterfaces(Type type)
        {
            if (!parentEntityDic.ContainsKey(type.FullName))
            {
                parentEntityDic.Add(type.FullName, new List<string>());
            }
            else
            {
                parentEntityDic[type.FullName] = new List<string>();
            }

            Type[] interfaceTypes = type.GetInterfaces();

            foreach (Type interfaceType in interfaceTypes)
            {
                if (typeof(IEntity).IsAssignableFrom(interfaceType) && interfaceType != typeof(IEntity))
                {
                    parentEntityDic[type.FullName].Add(interfaceType.FullName);
                }
            }
        }

        public class NodeComparer : System.Collections.IComparer
        {
            #region IComparer Members

            public int Compare(object x, object y)
            {
                return ((string)(x.GetType().GetProperty("Text").GetValue(x, null))).CompareTo((string)(y.GetType().GetProperty("Text").GetValue(y, null)));
            }

            #endregion
        }

        private void entityTree_AfterSelect(object sender, TreeViewEventArgs e)
        {
            removeSelectedAssemblyToolStripMenuItem.Enabled = false;

            if (e == null)
            {
                entityProperties.SelectedObject = null;
                return;
            }

            if (e.Node.Level == 1)
            {
                entityProperties.SelectedObject = entityDic[e.Node.Name];
            }
            else if (e.Node.Level == 2)
            {
                entityProperties.SelectedObject = columnDic[e.Node.Parent.Name][e.Node.Name];
            }
            else if (e.Node.Level == 0)
            {
                entityProperties.SelectedObject = null;
                removeSelectedAssemblyToolStripMenuItem.Enabled = true;
            }
            else
            {
                entityProperties.SelectedObject = null;
            }
        }

        private void RemoveLoadedAssembly(string nodeName)
        {
            entityTree.Nodes.RemoveByKey(nodeName);
        }

        private void removeSelectedAssemblyToolStripMenuItem_Click(object sender, EventArgs e)
        {
            RemoveLoadedAssembly(entityTree.SelectedNode.Name);
            entityTree.SelectedNode = null;
            entityTree_AfterSelect(null, null);
        }

        private void toolStripMenuItem1_Click(object sender, EventArgs e)
        {
            foreach (TreeNode node in entityTree.Nodes)
            {
                DoLoadAssembly(node.Text);
            }
        }

        private void toolStripMenuItem4_Click(object sender, EventArgs e)
        {
            foreach (TreeNode assNode in entityTree.Nodes)
            {
                foreach (TreeNode entityNode in assNode.Nodes)
                {
                    EntityConfiguration ec = entityDic[entityNode.Name];

                    if (!(ec.IsView && ec.UpdateTableName == null))
                    {
                        foreach (TreeNode columnNode in entityNode.Nodes)
                        {
                            if (string.IsNullOrEmpty(columnDic[entityNode.Name][columnNode.Text].DbType))
                            {
                                entityTree.SelectedNode = columnNode;
                                MessageBox.Show(string.Format("{0}.{1}.DbType cannot be null!", entityNode.Text, columnNode.Text));
                                return;
                            }
                        }
                    }
                }
            }

            MessageBox.Show("Check Ok! You can create database creation script now!");
        }

        private void loadConfigToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (openConfigFileDialog.ShowDialog() == DialogResult.OK)
            {
                DoLoadConfig(openConfigFileDialog.FileName);
            }
        }

        private void DoLoadConfig(string fileName)
        {
            XmlDocument doc = new XmlDocument();
            try
            {
                doc.Load(fileName);

                string sectionName = null;

                if (doc.SelectSingleNode("/configuration/configSections/section") != null)
                {
                    foreach (XmlNode node in doc.SelectNodes("/configuration/configSections/section"))
                    {
                        if (node.Attributes["type"].InnerXml.ToLower().Contains("NBear.Common.EntityConfigurationSection".ToLower()))
                        {
                            sectionName = node.Attributes["name"].InnerXml;
                            break;
                        }
                    }
                }

                if (sectionName == null)
                {
                    MessageBox.Show("The specified config file doesn't contain any entity configuration data!");
                    return;
                }

                XmlNode sectionNode = doc.SelectSingleNode("/configuration/" + sectionName);

                if (sectionNode != null)
                {
                    XmlNodeList entityNodes = sectionNode.SelectNodes("Entity");
                    if (entityNodes != null && entityNodes.Count > 0)
                    {
                        for (int i = 0; i < entityNodes.Count; i++)
                        {
                            string entityFullName = GetNodeValue(entityNodes[i].Attributes["fullName"]);
                            EntityConfiguration ec;
                            if (!entityDic.ContainsKey(entityFullName))
                            {
                                ec = new EntityConfiguration(entityFullName);
                                entityDic.Add(entityFullName, ec);
                            }
                            else
                            {
                                ec = entityDic[entityFullName];
                            }

                            XmlNode tableNode = entityNodes[i];
                            if (tableNode != null)
                            {
                                ec.TableName = GetNodeValue(tableNode.SelectSingleNode("Name"));
                                ec.AdditionalInsert = GetNodeValue(tableNode.SelectSingleNode("AdditionalInsert"));
                                ec.AdditionalWhere = GetNodeValue(tableNode.SelectSingleNode("AdditionalWhere"));
                                ec.IsCascadeUpdate = GetBoolNodeValue(tableNode.Attributes["isCascadeUpdate"]);
                                ec.IsContract = GetBoolNodeValue(tableNode.Attributes["isContract"]);
                                ec.UpdateTableName = GetNodeValue(tableNode.Attributes["updateTableName"]);
                                ec.IsView = GetBoolNodeValue(tableNode.Attributes["isView"]);
                                ec.FilterColumnName = GetNodeValue(tableNode.Attributes["filterColumnName"]);
                                ec.StoredProcedure = GetNodeValue(tableNode.Attributes["storedProcedure"]);
                                ec.StoredProcedureFillByIndex = GetBoolNodeValue(tableNode.Attributes["storedProcedureFillByIndex"]);
                                ec.IncludedInSqlGenerating = GetBoolNodeValue(tableNode.Attributes["includedInSqlGenerating"]);
                            }

                            XmlNode columnsNode = entityNodes[i].SelectSingleNode("Columns");
                            if (columnsNode != null)
                            {
                                XmlNodeList columnNodes = columnsNode.SelectNodes("Column");

                                if (columnNodes != null && columnNodes.Count > 0)
                                {
                                    if (!columnDic.ContainsKey(entityFullName))
                                    {
                                        columnDic.Add(entityFullName, new Dictionary<string, ColumnConfiguration>());
                                    }

                                    foreach (XmlNode columnNode in columnNodes)
                                    {
                                        string propertyName = GetNodeValue(columnNode.Attributes["propertyName"]);
                                        ColumnConfiguration cc;

                                        if (!columnDic[entityFullName].ContainsKey(propertyName))
                                        {
                                            cc = new ColumnConfiguration(propertyName);
                                            columnDic[entityFullName].Add(propertyName, cc);
                                        }
                                        else
                                        {
                                            cc = columnDic[entityFullName][propertyName];
                                        }

                                        cc.ColumnName = GetNodeValue(columnNode.Attributes["columnName"]);
                                        cc.IsPrimaryKey = GetBoolNodeValue(columnNode.Attributes["isPrimaryKey"]);
                                        cc.IsCustomProperty = GetBoolNodeValue(columnNode.Attributes["isCustomProperty"]);
                                        cc.DbType = GetNodeValue(columnNode.Attributes["dbType"]);

                                        XmlNode parametersNode = columnNode.SelectSingleNode("Parameters");

                                        if (parametersNode != null)
                                        {
                                            XmlNodeList parameterNodes = parametersNode.SelectNodes("Parameter");

                                            if (parameterNodes != null && parameterNodes.Count > 0)
                                            {
                                                cc.Parameters = new string[parameterNodes.Count];

                                                int k = 0;
                                                foreach (XmlNode parameterNode in parameterNodes)
                                                {
                                                    cc.Parameters[k] = GetNodeValue(parameterNode);

                                                    k++;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                entityTree.SelectedNode = null;
                entityProperties.SelectedObject = null;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }

        private string GetNodeValue(XmlNode node)
        {
            if (node != null)
            {
                return System.Web.HttpUtility.HtmlDecode(node.InnerXml);
            }

            return null;
        }

        private bool GetBoolNodeValue(XmlNode node)
        {
            string str = GetNodeValue(node);

            if (str != null && str.ToLower() == "true")
            {
                return true;
            }

            return false;
        }

        private void toolStripMenuItem2_Click(object sender, EventArgs e)
        {
            if (saveConfigFileDialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    XmlDocument doc = new XmlDocument();
                    if (System.IO.File.Exists(saveConfigFileDialog.FileName))
                    {
                        doc.Load(saveConfigFileDialog.FileName);
                    }
                    else
                    {
                        doc.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\" ?><configuration/>");
                    }

                    SerializeConfiguration(doc);

                    doc.Save(saveConfigFileDialog.FileName);
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        private void SerializeConfiguration(XmlDocument doc)
        {
            CheckConfigSectionsElement(doc);

            XmlNode sectionNode = GetEntityConfigurationElement(doc);
            sectionNode.RemoveAll();

            foreach (TreeNode assNode in entityTree.Nodes)
            {
                foreach (TreeNode entityNode in assNode.Nodes)
                {
                    EntityConfiguration ec = entityDic[entityNode.Name];

                    XmlNode xmlEntityNode = sectionNode.AppendChild(CreateElement(doc, "Entity", null));
                    xmlEntityNode.Attributes.Append(CreateAttribute(doc, "fullName", ec.FullName));
                    if (ec.IsView)
                    {
                        xmlEntityNode.Attributes.Append(CreateAttribute(doc, "isView", "true"));
                    }
                    if (ec.IsCascadeUpdate)
                    {
                        xmlEntityNode.Attributes.Append(CreateAttribute(doc, "isCascadeUpdate", "true"));
                    }
                    if (ec.IsContract)
                    {
                        xmlEntityNode.Attributes.Append(CreateAttribute(doc, "isContract", "true"));
                    }
                    if (!string.IsNullOrEmpty(ec.UpdateTableName))
                    {
                        xmlEntityNode.Attributes.Append(CreateAttribute(doc, "updateTableName", ec.UpdateTableName));
                    }
                    if (!string.IsNullOrEmpty(ec.FilterColumnName))
                    {
                        xmlEntityNode.Attributes.Append(CreateAttribute(doc, "filterColumnName", ec.FilterColumnName));
                    }
                    if (!string.IsNullOrEmpty(ec.StoredProcedure))
                    {
                        xmlEntityNode.Attributes.Append(CreateAttribute(doc, "storedProcedure", ec.StoredProcedure));
                    }
                    if (ec.StoredProcedureFillByIndex)
                    {
                        xmlEntityNode.Attributes.Append(CreateAttribute(doc, "storedProcedureFillByIndex", "true"));
                    }
                    if (ec.IncludedInSqlGenerating)
                    {
                        xmlEntityNode.Attributes.Append(CreateAttribute(doc, "includedInSqlGenerating", "true"));
                    }
                    if (!string.IsNullOrEmpty(ec.TableName))
                    {
                        xmlEntityNode.AppendChild(CreateElement(doc, "Name", ec.TableName));
                    }
                    if (!string.IsNullOrEmpty(ec.AdditionalWhere))
                    {
                        xmlEntityNode.AppendChild(CreateElement(doc, "AdditionalWhere", ec.AdditionalWhere));
                    }
                    if (!string.IsNullOrEmpty(ec.AdditionalInsert))
                    {
                        xmlEntityNode.AppendChild(CreateElement(doc, "AdditionalInsert", ec.AdditionalInsert));
                    }

                    XmlNode xmlColumnsNode = xmlEntityNode.AppendChild(CreateElement(doc, "Columns", null));

                    foreach (TreeNode columnNode in entityNode.Nodes)
                    {
                        ColumnConfiguration cc = columnDic[entityNode.Name][columnNode.Name];

                        if ((!string.IsNullOrEmpty(cc.ColumnName) || (!string.IsNullOrEmpty(cc.DbType) || cc.IsCustomProperty || cc.IsPrimaryKey || (cc.Parameters != null && cc.Parameters.Length > 0))))
                        {
                            XmlNode xmlColumnNode = xmlColumnsNode.AppendChild(CreateElement(doc, "Column", null));
                            xmlColumnNode.Attributes.Append(CreateAttribute(doc, "propertyName", cc.PropertyName));
                            if (!string.IsNullOrEmpty(cc.ColumnName))
                            {
                                xmlColumnNode.Attributes.Append(CreateAttribute(doc, "columnName", cc.ColumnName));
                            }
                            if (!string.IsNullOrEmpty(cc.DbType))
                            {
                                xmlColumnNode.Attributes.Append(CreateAttribute(doc, "dbType", cc.DbType));
                            }
                            if (cc.IsPrimaryKey)
                            {
                                xmlColumnNode.Attributes.Append(CreateAttribute(doc, "isPrimaryKey", "true"));
                            }
                            if (cc.IsCustomProperty)
                            {
                                xmlColumnNode.Attributes.Append(CreateAttribute(doc, "isCustomProperty", "true"));
                            }

                            if (cc.IsCustomProperty && cc.Parameters != null && cc.Parameters.Length > 0)
                            {
                                XmlNode xmlParamsNode = xmlColumnNode.AppendChild(CreateElement(doc, "Parameters", null));

                                foreach (string item in cc.Parameters)
                                {
                                    xmlParamsNode.AppendChild(CreateElement(doc, "Parameter", item));
                                }
                            }
                        }
                    }

                    if (xmlColumnsNode.ChildNodes.Count == 0)
                    {
                        xmlEntityNode.RemoveChild(xmlColumnsNode);
                    }

                    if (xmlEntityNode.Attributes.Count <= 1 && xmlEntityNode.ChildNodes.Count == 0)
                    {
                        xmlEntityNode.ParentNode.RemoveChild(xmlEntityNode);
                    }
                }
            }
        }

        private XmlNode GetEntityConfigurationElement(XmlDocument doc)
        {
            XmlNode configSectionsNode = doc.SelectSingleNode("/configuration/configSections");
            string sectionName = null;

            foreach (XmlNode node in configSectionsNode.SelectNodes("section"))
            {
                if (node.Attributes["type"].InnerXml.ToLower().Contains("NBear.Common.EntityConfigurationSection".ToLower()))
                {
                    sectionName = node.Attributes["name"].InnerXml;
                    break;
                }
            }

            if (sectionName == null)
            {
                XmlNode sectionNode = doc.CreateElement("section");
                sectionNode.Attributes.Append(CreateAttribute(doc, "name", "EntityConfiguration"));
                sectionNode.Attributes.Append(CreateAttribute(doc, "type", "NBear.Common.EntityConfigurationSection, NBear.Common"));
                configSectionsNode.AppendChild(sectionNode);
                return doc.SelectSingleNode("/configuration").AppendChild(doc.CreateElement("EntityConfiguration"));
            }

            return doc.SelectSingleNode("/configuration/" + sectionName);
        }

        private static XmlAttribute CreateAttribute(XmlDocument doc, string name, string value)
        {
            XmlAttribute attr = doc.CreateAttribute(name);
            attr.InnerXml = (value == null ? string.Empty : value);
            return attr;
        }

        private static XmlElement CreateElement(XmlDocument doc, string name, string value)
        {
            XmlElement node = doc.CreateElement(name);
            if (value != null)
            {
                node.InnerXml = value;
            }
            return node;
        }

        private void CheckConfigSectionsElement(XmlDocument doc)
        {
            if (doc.SelectSingleNode("/configuration/configSections") == null)
            {
                if (doc.SelectSingleNode("/configuration").ChildNodes.Count == 0)
                {
                    doc.SelectSingleNode("/configuration").AppendChild(doc.CreateElement("configSections"));
                }
                else
                {
                    doc.SelectSingleNode("/configuration").InsertBefore(doc.CreateElement("configSections"), doc.SelectSingleNode("/configuration").FirstChild);
                }
            }
        }

        private void generateDatabaseScriptToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (saveSqlFileDialog.ShowDialog() == DialogResult.OK)
            {
                StringBuilder sb = new StringBuilder();
                Dictionary<string, List<string>> tables = new Dictionary<string, List<string>>();

                foreach (TreeNode assNode in entityTree.Nodes)
                {
                    foreach (TreeNode entityNode in assNode.Nodes)
                    {
                        EntityConfiguration ec = entityDic[entityNode.Name];
                        if (string.IsNullOrEmpty(ec.TableName))
                        {
                            ec.TableName = entityNode.Text;
                        }

                        if (ec.IncludedInSqlGenerating)
                        {
                            //for creating primary key
                            StringBuilder sbPK = new StringBuilder();
                            //for creating index
                            StringBuilder sbIndex = new StringBuilder();
                            //for creating table
                            StringBuilder sbTable = new StringBuilder();

                            if (tables.ContainsKey(ec.UpdateTableName ?? ec.TableName))
                            {
                                //add columns to table
                                sbTable.Append(string.Format("ALTER TABLE [dbo].[{0}] ADD\r\n", ec.UpdateTableName ?? ec.TableName));

                                //add extended columns
                                foreach (TreeNode columnNode in entityNode.Nodes)
                                {
                                    ColumnConfiguration cc = columnDic[entityNode.Name][columnNode.Name];

                                    if ((!cc.IsPrimaryKey) && (!tables[ec.UpdateTableName ?? ec.TableName].Contains(columnNode.Name)))
                                    {
                                        tables[ec.UpdateTableName ?? ec.TableName].Add(columnNode.Name);

                                        sbTable.Append(string.Format("[{0}] {1}{2},\r\n", cc.ColumnName ?? cc.PropertyName, cc.DbType, cc.IsPrimaryKey ? " NOT NULL" : string.Empty));
                                    }
                                }

                                sbTable.Append("GO\r\n\r\n");

                                if (!sbTable.ToString().Contains("ADD\r\nGO"))
                                {
                                    sb.Append(sbTable.ToString().Replace(",\r\nGO", "\r\nGO"));
                                }
                            }
                            else
                            {
                                tables.Add(ec.UpdateTableName ?? ec.TableName, new List<string>());

                                //create table
                                sbTable.Append(string.Format("CREATE TABLE [dbo].[{0}] (\r\n", ec.UpdateTableName ?? ec.TableName));

                                //create primary key
                                sbPK.Append(string.Format("ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD\r\nCONSTRAINT [{1}] PRIMARY KEY CLUSTERED\r\n(\r\n", ec.UpdateTableName ?? ec.TableName, "PK_" + (ec.UpdateTableName ?? ec.TableName).Replace(" ", "_")));

                                List<string> extendedColumns = new List<string>();
                                Dictionary<string, List<string>> inherittedColumns = new Dictionary<string, List<string>>();

                                //add extended columns
                                foreach (TreeNode columnNode in entityNode.Nodes)
                                {
                                    tables[ec.UpdateTableName ?? ec.TableName].Add(columnNode.Name);

                                    ColumnConfiguration cc = columnDic[entityNode.Name][columnNode.Name];

                                    GenerateColumn(ec, sbPK, sbIndex, sbTable, cc);

                                    extendedColumns.Add(columnNode.Name);
                                }

                                //add inherited columns
                                foreach (string parentFullName in parentEntityDic[ec.FullName])
                                {
                                    EntityConfiguration parentEc = entityDic[parentFullName];

                                    TreeNode parentEntityNode = entityTree.Nodes.Find(parentFullName, true)[0];

                                    foreach (TreeNode columnNode in parentEntityNode.Nodes)
                                    {
                                        if (!IsColumnExists(extendedColumns, inherittedColumns, columnNode.Text))
                                        {
                                            tables[ec.UpdateTableName ?? ec.TableName].Add(columnNode.Name);

                                            ColumnConfiguration cc = columnDic[parentEntityNode.Name][columnNode.Name];

                                            if (parentEc.IsContract || (!ec.IsCascadeUpdate) || cc.IsPrimaryKey || (ec.IsCascadeUpdate && string.IsNullOrEmpty(ec.UpdateTableName)))
                                            {
                                                GenerateColumn(ec, sbPK, sbIndex, sbTable, cc);

                                                if (!inherittedColumns.ContainsKey(parentFullName))
                                                {
                                                    inherittedColumns.Add(parentFullName, new List<string>());
                                                }

                                                if (!inherittedColumns[parentFullName].Contains(cc.PropertyName))
                                                {
                                                    inherittedColumns[parentFullName].Add(cc.PropertyName);
                                                }
                                            }
                                        }
                                    }
                                }

                                if ((!string.IsNullOrEmpty(ec.FilterColumnName)) && (!IsColumnExists(extendedColumns, inherittedColumns, ec.FilterColumnName)) && (!tables[ec.UpdateTableName ?? ec.TableName].Contains(ec.FilterColumnName)))
                                {
                                    tables[ec.UpdateTableName ?? ec.TableName].Add(ec.FilterColumnName);

                                    sbTable.Append(string.Format("[{0}] int ,\r\n", ec.FilterColumnName));
                                }

                                sbTable.Append(") ON [PRIMARY]\r\nGO\r\n\r\n");
                                sbPK.Append(") ON [PRIMARY]\r\nGO\r\n\r\n");

                                sb.Append(sbTable.ToString().Replace(",\r\n)", "\r\n)"));

                                if (!sbPK.ToString().Contains("(\r\n)"))
                                {
                                    sb.Append(sbPK.ToString().Replace(",\r\n)", "\r\n)"));
                                    sb.Append(sbIndex);
                                }
                            }
                        }
                    }
                }

                System.IO.StreamWriter sw = new System.IO.StreamWriter(saveSqlFileDialog.FileName);
                foreach (string table in tables.Keys)
                {
                    //delete existing table
                    sw.Write(string.Format("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)\r\ndrop table [dbo].[{0}]\r\nGO\r\n\r\n", table));
                }
                sw.Write(sb.ToString());
                sw.Close();
            }
        }

        private bool IsColumnExists(List<string> extendedColumns, Dictionary<string, List<string>> inherittedColumns, string propertyName)
        {
            bool retVal = false;

            retVal = extendedColumns.Contains(propertyName);

            if (retVal)
            {
                return true;
            }

            foreach (List<string> columnNames in inherittedColumns.Values)
            {
                retVal = columnNames.Contains(propertyName);

                if (retVal)
                {
                    return true;
                }
            }

            return retVal;
        }

        private static void GenerateColumn(EntityConfiguration ec, StringBuilder sbPK, StringBuilder sbIndex, StringBuilder sbTable, ColumnConfiguration cc)
        {
            if (!cc.IsCustomProperty)
            {
                sbTable.Append(string.Format("[{0}] {1}{2},\r\n", cc.ColumnName ?? cc.PropertyName, cc.DbType, cc.IsPrimaryKey ? " NOT NULL" : string.Empty));

                if (cc.IsPrimaryKey)
                {
                    sbPK.Append(string.Format("[{0}],\r\n", cc.ColumnName ?? cc.PropertyName));

                    sbIndex.Append(string.Format("CREATE INDEX [{0}] ON [dbo].[{1}]([{0}]) ON [PRIMARY]\r\nGO\r\n\r\n", cc.ColumnName ?? cc.PropertyName, ec.UpdateTableName ?? ec.TableName));
                }
            }
        }

        private void generateEntityQueryCodeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            EntityQueryCodeForm form = new EntityQueryCodeForm();

            form.output.Text = BuildEntityQueryCode("C#");

            form.ShowDialog();
        }

        private string BuildEntityQueryCode(string language)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbEntity = new StringBuilder();

            if (language == "C#")
            {
                sb.Append("    namespace _Entity\r\n    {\r\n");

                foreach (TreeNode assNode in entityTree.Nodes)
                {
                    foreach (TreeNode entityNode in assNode.Nodes)
                    {
                        sbEntity.Append(string.Format("        public class {0}\r\n", entityNode.Text));
                        sbEntity.Append("        {\r\n");

                        List<string> generatedColumns = new List<string>();

                        foreach (TreeNode columnNode in entityNode.Nodes)
                        {
                            sbEntity.Append(string.Format("            public static PropertyItem {0} = new PropertyItem(\"{0}\");\r\n", columnNode.Text));

                            generatedColumns.Add(columnNode.Text);
                        }

                        if (parentEntityDic.ContainsKey(entityNode.Name))
                        {
                            if (parentEntityDic[entityNode.Name].Count > 0)
                            {
                                foreach (string parentFullName in parentEntityDic[entityNode.Name])
                                {
                                    TreeNode parentEntityNode = entityTree.Nodes.Find(parentFullName, true)[0];

                                    foreach (TreeNode columnNode in parentEntityNode.Nodes)
                                    {
                                        if (!generatedColumns.Contains(columnNode.Text))
                                        {
                                            sbEntity.Append(string.Format("            public static PropertyItem {0} = new PropertyItem(\"{0}\");\r\n", columnNode.Text));

                                            generatedColumns.Add(columnNode.Text);
                                        }
                                    }
                                }
                            }
                        }

                        sbEntity.Append("        }\r\n\r\n");
                    }
                }

                sb.Append(sbEntity.ToString());

                sb.Append("    }\r\n");
            }
            else if (language == "VB.NET")
            {
                sb.Append("    Namespace _Entity\r\n\r\n");

                foreach (TreeNode assNode in entityTree.Nodes)
                {
                    foreach (TreeNode entityNode in assNode.Nodes)
                    {
                        sbEntity.Append(string.Format("        Public Class {0}\r\n", entityNode.Text));

                        List<string> generatedColumns = new List<string>();

                        foreach (TreeNode columnNode in entityNode.Nodes)
                        {
                            sbEntity.Append(string.Format("            Public Shared {0} As PropertyItem = New PropertyItem(\"{0}\")\r\n", columnNode.Text));

                            generatedColumns.Add(columnNode.Text);
                        }

                        if (parentEntityDic.ContainsKey(entityNode.Name))
                        {
                            if (parentEntityDic[entityNode.Name].Count > 0)
                            {
                                foreach (string parentFullName in parentEntityDic[entityNode.Name])
                                {
                                    TreeNode parentEntityNode = entityTree.Nodes.Find(parentFullName, true)[0];

                                    foreach (TreeNode columnNode in parentEntityNode.Nodes)
                                    {
                                        if (!generatedColumns.Contains(columnNode.Text))
                                        {
                                            sbEntity.Append(string.Format("            Public Shared {0} As PropertyItem = New PropertyItem(\"{0}\")\r\n", columnNode.Text));

                                            generatedColumns.Add(columnNode.Text);
                                        }
                                    }
                                }
                            }
                        }

                        sbEntity.Append("        End Class\r\n\r\n");
                    }
                }

                sb.Append(sbEntity.ToString());

                sb.Append("    End Namespace\r\n");
            }

            return sb.ToString();
        }

        private void entityProperties_Click(object sender, EventArgs e)
        {

        }

        private void generateEntityQueryCodeVBNETToolStripMenuItem_Click(object sender, EventArgs e)
        {
            EntityQueryCodeForm form = new EntityQueryCodeForm();

            form.output.Text = BuildEntityQueryCode("VB.NET");

            form.ShowDialog();
        }
    }
}